import pandas as pd
from datetime import datetime, timedelta

# 读取Excel文件
file_path = 'python\\sh000300\\定投方式对比\\000300perf_left.xlsx'  # 请确保文件路径正确
df = pd.read_excel(file_path)

# 确保日期列是datetime类型
df['日期Date'] = pd.to_datetime(df['日期Date'], format='%Y%m%d')

# 定投金额设置
daily_investment = 100  # 日定投金额为100元

# 初始化结果存储
results = []

# 日定投计算
daily_results = []
cumulative_investment = 0
for index, row in df.iterrows():
    cumulative_investment += daily_investment
    daily_results.append({
        'Date': row['日期Date'],
        'Investment': daily_investment,
        'Cumulative_Investment': cumulative_investment,
        'Closing_Price': row['收盘Close']
    })

# 周定投计算
weekly_results = []
current_week = df.iloc[0]['日期Date'].week
weekly_investment = 0
cumulative_investment = 0
for index, row in df.iterrows():
    if row['日期Date'].week != current_week:
        cumulative_investment += weekly_investment
        weekly_results.append({
            'Date': df.iloc[index - 1]['日期Date'],
            'Investment': weekly_investment,
            'Cumulative_Investment': cumulative_investment,
            'Closing_Price': df.iloc[index - 1]['收盘Close']
        })
        current_week = row['日期Date'].week
        weekly_investment = daily_investment
    else:
        weekly_investment += daily_investment

# 添加最后一周的数据
cumulative_investment += weekly_investment
weekly_results.append({
    'Date': df.iloc[-1]['日期Date'],
    'Investment': weekly_investment,
    'Cumulative_Investment': cumulative_investment,
    'Closing_Price': df.iloc[-1]['收盘Close']
})

# 月定投计算
monthly_results = []
current_month = df.iloc[0]['日期Date'].month
monthly_investment = 0
cumulative_investment = 0
for index, row in df.iterrows():
    if row['日期Date'].month != current_month:
        cumulative_investment += monthly_investment
        monthly_results.append({
            'Date': df.iloc[index - 1]['日期Date'],
            'Investment': monthly_investment,
            'Cumulative_Investment': cumulative_investment,
            'Closing_Price': df.iloc[index - 1]['收盘Close']
        })
        current_month = row['日期Date'].month
        monthly_investment = daily_investment
    else:
        monthly_investment += daily_investment

# 添加最后一个月的数据
cumulative_investment += monthly_investment
monthly_results.append({
    'Date': df.iloc[-1]['日期Date'],
    'Investment': monthly_investment,
    'Cumulative_Investment': cumulative_investment,
    'Closing_Price': df.iloc[-1]['收盘Close']
})

# 将结果转换为DataFrame
daily_df = pd.DataFrame(daily_results)
weekly_df = pd.DataFrame(weekly_results)
monthly_df = pd.DataFrame(monthly_results)

# 计算总收益和收益率
daily_df['Total_Return'] = daily_df['Closing_Price'] * (daily_df['Investment'] / daily_df['Closing_Price']).cumsum()
daily_df['Return_Rate'] = (daily_df['Total_Return'] - daily_df['Cumulative_Investment']) / daily_df['Cumulative_Investment']

weekly_df['Total_Return'] = weekly_df['Closing_Price'] * (weekly_df['Investment'] / weekly_df['Closing_Price']).cumsum()
weekly_df['Return_Rate'] = (weekly_df['Total_Return'] - weekly_df['Cumulative_Investment']) / weekly_df['Cumulative_Investment']

monthly_df['Total_Return'] = monthly_df['Closing_Price'] * (monthly_df['Investment'] / monthly_df['Closing_Price']).cumsum()
monthly_df['Return_Rate'] = (monthly_df['Total_Return'] - monthly_df['Cumulative_Investment']) / monthly_df['Cumulative_Investment']

# 保存到Excel
with pd.ExcelWriter('python\\sh000300\\定投方式对比\\investment_left_results.xlsx') as writer:
    daily_df.to_excel(writer, sheet_name='Daily_Investment', index=False)
    weekly_df.to_excel(writer, sheet_name='Weekly_Investment', index=False)
    monthly_df.to_excel(writer, sheet_name='Monthly_Investment', index=False)

print("投资结果已保存到 'investment_results.xlsx'")